package com.junzhiit.nengji.shop.dao.impl;

import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.shop.bo.GoodsBO;
import com.junzhiit.nengji.shop.bo.GoodsListBO;
import com.junzhiit.nengji.shop.bo.ShopBO;
import com.junzhiit.nengji.shop.bo.ShopDetailsBO;
import com.junzhiit.nengji.shop.bo.ShopInfoBO;
import com.junzhiit.nengji.shop.bo.ShopWithinDistanceBO;
import com.junzhiit.nengji.shop.dao.ShopDAO;
import com.junzhiit.nengji.shop.model.Shop;
import com.junzhiit.nengji.shop.model.enums.AuditStatus;
import com.junzhiit.nengji.shop.model.enums.GoodsImagesType;
import com.junzhiit.nengji.shop.model.enums.GoodsStatus;
import com.junzhiit.nengji.shop.model.enums.Is;
import com.junzhiit.nengji.shop.model.enums.OrderType;
import com.junzhiit.nengji.shop.model.enums.ShopIsPlatform;
import com.junzhiit.nengji.shop.model.enums.ShopStatus;

@Repository
public class ShopDAOImpl extends AbstractBaseDAO<Shop> implements ShopDAO {

	@Override
	public int checkMainAccount(String mainAccounId) {
		String sql = "select count(*) from shop where main_account_id =? and is_delete =?";
		StatementParameter params = new StatementParameter();
		params.setString(mainAccounId);
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForInt(sql, params);
	}

	@Override
	public List<ShopBO> findAll(Shop shop, int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("select s.id as shopId,s.name,s.description,s.address,si.identity_name,s.mobile_phone,s.is_delivery");
		sql.append(" ,i.full_name as industryName,s.status,su.status as systemUserStatus,s.lat,s.lng,s.is_recent_change");
		sql.append(" ,s.is_shop,s.is_set_type,s.is_mail,s.audit_status,s.expire_time as expireTime,s.is_eat,s.is_reserve");
		sql.append(" ,s.profit,s.linkman,s.is_chain,sm.name as setMealName,s.is_open_sell,s.is_open_rebate,s.is_platform ");
		sql.append(" from shop s");
		sql.append(" inner join shop_info si on s.id = si.shop_id");
		sql.append(" inner join system_user su on s.id = su.system_id and su.is_main_account = ?");
		sql.append(" inner join industry i on i.id = s.industry_id");
		sql.append(" inner join set_meal sm on sm.id=s.set_meal_id");
		sql.append(" where s.is_delete =? ");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.YES.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		
		if (shop.getIsPlatform() != null) {
			sql.append(" and s.is_platform = ? ");
			params.setInt(shop.getIsPlatform());
		}
		
		if (StringUtils.isNoneBlank(shop.getName())) {
			sql.append(" and s.name like ?");
			params.setString("%" + shop.getName() + "%");
		}
		if (shop.getAuditStatus() != null) {
			sql.append(" and s.audit_status = ?");
			params.setInt(shop.getAuditStatus());
		}
		if (StringUtils.isNoneBlank(shop.getChainId())) {
			sql.append(" and s.chain_id = ?");
			params.setString(shop.getChainId());
		}
		if(shop.getIsChain()!=null){
			sql.append(" and s.is_chain=?");
			params.setInt(shop.getIsChain());
		}
		return super.queryForPagingList(sql.toString(), ShopBO.class, params, pageNumber, maxPageRow);
	}

	@Override
	public int getTotalCount(Shop shop) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(s.id) from shop s");
		sql.append(" inner join shop_info si on s.id = si.shop_id");
		sql.append(" inner join system_user su on s.id = su.system_id and su.is_main_account = ?");
		sql.append(" inner join industry i on i.id = s.industry_id");
		sql.append(" inner join set_meal sm on sm.id=s.set_meal_id");
		sql.append(" where s.is_delete =? ");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.YES.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		
		if (shop.getIsPlatform() != null) {
			sql.append(" and s.is_platform = ? ");
			params.setInt(shop.getIsPlatform());
		}
		
		if (StringUtils.isNoneBlank(shop.getName())) {
			sql.append(" and s.name like ?");
			params.setString("%" + shop.getName() + "%");
		}
		if (shop.getAuditStatus() != null) {
			sql.append(" and s.audit_status = ?");
			params.setInt(shop.getAuditStatus());
		}
		if (StringUtils.isNoneBlank(shop.getChainId())) {
			sql.append(" and s.chain_id = ?");
			params.setString(shop.getChainId());
		}
		if(shop.getIsChain()!=null){
			sql.append(" and s.is_chain=?");
			params.setInt(shop.getIsChain());
		}
		return super.queryForInt(sql.toString(), params);
	}

	@Override
	public ShopBO getShopById(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append("select s.id as shopId ,si.id as shopInfoId,s.name,s.address,s.description,s.mobile_phone,s.lat,s.lng,s.linkman,s.profit");
		sql.append(" ,i.full_name as industryName,s.status,s.type,si.*,s.industry_id,s.is_shop,s.is_delivery");
		sql.append(" ,s.is_mail,s.expire_time,s.is_open_sell,s.is_open_rebate,s.is_platform,s.is_eat,s.is_reserve,s.is_custom_goods,");
		sql.append(" (select a.`name` from area a where a.id=s.area_province_id) province_name,");
		sql.append(" (select a.`name` from area a where a.id=s.area_city_id) city_name,");
		sql.append(" (select a.`name` from area a where a.id=s.area_county_id) county_name");
		sql.append(" from shop s ");
		sql.append(" inner join shop_info si on s.id = si.shop_id ");
		sql.append(" inner join industry i on i.id = s.industry_id");
		sql.append(" where s.id=? and s.is_delete =?");
		StatementParameter params = new StatementParameter();
		params.setString(id);
		params.setInt(IsDelete.NO.toIntValue());
		return super.query(sql.toString(), ShopBO.class, params);
	}

	@Override
	public List<Shop> getShopList() {
		String sql = "select id,name from shop where is_delete =?";
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());

		return super.queryForList(sql, Shop.class, params);
	}

	@Override
	public ShopBO findShopById(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append("select s.id as shopId,s.background_image,s.shop_head_img_url,s.collections,s.opening_days,");
		sql.append("s.name,s.address,s.description,s.mobile_phone,i.full_name as industryName,s.opening_hours,");
		sql.append("s.status,s.type,s.industry_id,s.banner_images,s.is_platform,");
		sql.append("(select name from area where is_delete=? and id=s.area_province_id) 'province',");
		sql.append("(select name from area where is_delete=? and id=s.area_city_id) 'city',s.lat,s.lng,");
		sql.append("(select name from area where is_delete=? and id=s.area_county_id) 'county'");
		sql.append(" from shop s ");
		sql.append(" inner join industry i on i.id = s.industry_id");
		sql.append(" where s.is_delete =?");

		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());

		if (StringUtils.isNotBlank(id)) {
			sql.append(" and s.id=?");
			params.setString(id);
		}

		return query(sql.toString(), ShopBO.class, params);
	}

	@Override
	public ShopInfoBO findById(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select s.*,");
		sql.append(" (select a.`name` from area a where a.id=s.area_province_id) province_name,");
		sql.append(" (select a.`name` from area a where a.id=s.area_city_id) city_name,");
		sql.append(" (select a.`name` from area a where a.id=s.area_county_id) county_name,");
		sql.append(" (select name from set_meal where id=s.set_meal_id) setMealId");
		sql.append(" from shop s");
		sql.append(" where s.id=?");
		StatementParameter params = new StatementParameter();
		params.setString(id);
		return query(sql.toString(), ShopInfoBO.class, params);
	}

	@Override
	public int checkShop(String shopId) {
		StatementParameter params = new StatementParameter();
		StringBuilder sql = new StringBuilder();
		sql.append("select count(id) from shop where id = ? and status = ? and is_delete = ?");
		params.setString(shopId);
		params.setInt(ShopStatus.ACTIVE.toIntStatus());
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForInt(sql.toString(), params);
	}

	@Override
	public int checkMaxCollections(String shopId) {
		String sql = "select max(collections) from shop where id =?";
		StatementParameter params = new StatementParameter();
		params.setString(shopId);
		return super.queryForInt(sql, params);
	}

	@Override
	public int getCountByShop() {
		StringBuilder sql = new StringBuilder();
		sql.append("select count(s.id) from shop s");
		sql.append(" inner join system_user su on s.id = su.system_id and su.is_main_account = ?");
		sql.append(" where s.audit_status = ? and s.is_delete = ? ");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.YES.toIntValue());
		params.setInt(AuditStatus.AUDITSUCCESS.toIntStatus());
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForInt(sql.toString(), params);
	}

	@Override
	public int getCountByShopApproval() {
		StringBuilder sql = new StringBuilder();
		sql.append("select count(s.id) from shop s");
		sql.append(" inner join system_user su on s.id = su.system_id and su.is_main_account = ?");
		sql.append(" where s.audit_status = ? and s.is_delete = ?");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.YES.toIntValue());
		params.setInt(AuditStatus.AUDIT.toIntStatus());
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForInt(sql.toString(), params);
	}

	@Override
	public List<Shop> getAllShop() {
		String sql = "select * from shop where is_delete = ? and is_expire = ?";
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(Is.NO.toIntValue());
		return super.queryForList(sql, Shop.class, params);
	}

	@Override
	public List<Shop> getSoonExpireShop() {
		String sql = "select * from shop where is_delete = ? and is_expire = ? and is_remind = ?";
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(Is.NO.toIntValue());
		params.setInt(Is.NO.toIntValue());
		return super.queryForList(sql, Shop.class, params);
	}

	@Override
	public List<Shop> getGoodsAuditShopSelect() {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();

		sql.append("select distinct s.* from shop s inner join goods g");
		sql.append(" on s.id=g.shop_id and g.status=? and s.is_delete=? and g.is_delete=? and s.is_chain = ?");

		param.setInt(GoodsStatus.AUDIT.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(Is.NO.toIntValue());

		return queryForList(sql.toString(), Shop.class, param);
	}

	@Override
	public List<Shop> getShopList(Integer type, String chainId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();

		sql.append("select * from shop where is_delete=?");
		param.setInt(IsDelete.NO.toIntValue());
		
		if(type != null){
			if(type == OrderType.SHOP.toIntValue()){
				sql.append(" and is_shop = ?");
				param.setInt(Is.YES.toIntValue());
			}
			if(type == OrderType.DELIVERY.toIntValue() || type == OrderType.EXTRACT.toIntValue()){
				sql.append(" and is_delivery = ?");
				param.setInt(Is.YES.toIntValue());
			}
			if(type == OrderType.EAT.toIntValue()){
				sql.append(" and is_eat = ?");
				param.setInt(Is.YES.toIntValue());
			}
			
			if(type == OrderType.MAIL.toIntValue()){
				sql.append(" and is_mail = ?");
				param.setInt(Is.YES.toIntValue());
			}
			
			if(type == OrderType.RESERVE.toIntValue()){
				sql.append(" and is_reserve = ?");
				param.setInt(Is.YES.toIntValue());
			}
		}

		if (StringUtils.isNotBlank(chainId)) {
			sql.append(" and chain_id = ?");
			param.setString(chainId);
		}

		return queryForList(sql.toString(), Shop.class, param);
	}

	@Override
	public List<ShopBO> findShopList(Shop shop) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select *,");
		sql.append(" (select name from area where id=s.area_province_id) province,");
		sql.append(" (select name from area where id=s.area_city_id) city,");
		sql.append(" (select name from area where id=s.area_county_id) county,");
		sql.append(" (select full_name from industry where id=s.industry_id) industryName");
		sql.append(" from shop s");
		sql.append(" where is_delete=?");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());

		if (StringUtils.isNotBlank(shop.getMainAccountId())) {
			sql.append(" and main_account_id=?");
			param.setString(shop.getMainAccountId());
		}

		return super.queryForList(sql.toString(), ShopBO.class, param);

	}

	@Override
	public List<Shop> getExpireShop() {
		String sql = "select * from shop where is_delete = ? and is_expire = ? and is_remind_expire = ?";
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(Is.YES.toIntValue());
		params.setInt(Is.NO.toIntValue());
		return super.queryForList(sql, Shop.class, params);
	}

	@Override
	public String getMainAccountPhone(String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select su.mobile_phone from shop s");
		sql.append(" inner join system_user su on s.id=su.system_id and su.is_main_account = ?");
		sql.append(" where s.is_delete = ? and s.id = ?");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.YES.toIntValue());
		params.setInt(Is.NO.toIntValue());
		params.setString(shopId);
		return super.queryForString(sql.toString(), params);
	}

	@Override
	public List<Shop> getRecommendByUserId(String userId, int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from shop where user_id = ? and is_delete = ?");
		StatementParameter params = new StatementParameter();
		params.setString(userId);
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForPagingList(sql.toString(), Shop.class, params, pageNumber, maxPageRow);
	}

	@Override
	public int getRecommendTotalCount(String userId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(id) from shop where user_id = ? and is_delete = ?");
		StatementParameter params = new StatementParameter();
		params.setString(userId);
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForInt(sql.toString(), params);
	}

	@Override
	public List<ShopBO> findListByChain(String systemUserId,Shop shop,int pageNumber, int maxPageRow){
		StringBuffer sql=new StringBuffer();
		sql.append(" select ");
		sql.append(" s.*,");
		sql.append(" su.mobile_phone account,");
		sql.append(" (select name from area where id=s.area_province_id) province,");
		sql.append(" (select name from area where id=s.area_city_id) city,");
		sql.append(" (select name from area where id=s.area_county_id) county,");
		sql.append(" (select full_name from industry where id=s.industry_id) industryName,");
		sql.append(" (select name from set_meal where id=s.set_meal_id) setMealName");
		sql.append(" from `chain` c");
		sql.append(" inner join shop s on s.chain_id=c.id");
		sql.append(" inner join system_user su on su.id=s.main_account_id");
		sql.append(" where s.is_delete=?");
		sql.append(" and c.system_user_id=?");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(systemUserId);

		if (StringUtils.isNoneBlank(shop.getName())) {
			sql.append(" and s.name like ?");
			param.setString("%" + shop.getName() + "%");
		}

		return queryForPagingList(sql.toString(), ShopBO.class, param, pageNumber, maxPageRow);

	}

	@Override
	public int getTotalCountByChain(String systemUserId, Shop shop) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(*) from `chain` c");
		sql.append(" inner join shop s on s.chain_id=c.id");
		sql.append(" inner join system_user su on su.id=s.main_account_id");
		sql.append(" where s.is_delete=?");
		sql.append(" and c.system_user_id=?");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(systemUserId);

		if (StringUtils.isNoneBlank(shop.getName())) {
			sql.append(" and s.name like ?");
			param.setString("%" + shop.getName() + "%");
		}

		return queryForInt(sql.toString(), param);

	}

	@Override
	public List<ShopBO> findConstrictAll() {
		StringBuffer sql = new StringBuffer();
		sql.append("select s.id as shopId,s.name ");
		sql.append(" from shop s");
		sql.append(" inner join shop_info si on s.id = si.shop_id");
		sql.append(" inner join system_user su on s.id = su.system_id and su.is_main_account = ?");
		sql.append(" inner join industry i on i.id = s.industry_id");
		sql.append(" where s.is_delete =?");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.YES.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForList(sql.toString(), ShopBO.class, params);
	}

	@Override
	public List<ShopDetailsBO> findShops(String shopName) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select *,s.id shopId,");
		sql.append(" (select name from area where id=s.area_province_id) province, ");
		sql.append(" (select name from area where id=s.area_city_id) city, ");
		sql.append(" (select name from area where id=s.area_county_id) county, ");
		sql.append(" (select full_name from industry where id=s.industry_id) industryName, ");
		sql.append(" (select ifnull(round(avg(score),1),0) from user_evaluate u where u.shop_id=s.id) score ");
		sql.append(" from shop s ");
		sql.append(" where s.is_delete=? ");
		sql.append("  and s.name like ? ");
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		params.setString("%" + shopName + "%");
		return super.queryForList(sql.toString(), ShopDetailsBO.class, params);
	}

	@Override
	public List<GoodsBO> findGoodsListByShopId(GoodsListBO glb, Integer pageNumber, Integer maxPageRow) {
		StringBuffer sql=new StringBuffer();
		sql.append("select g.*,");
		sql.append(" gi.file_name fileName,b.`name` brand_name,t.`name` type_name,");
		
		sql.append(" (case g.is_open_sku ");
		sql.append(" when 1 then (select case min(sc.price)=max(sc.price) when 0 then concat_ws('~',min(sc.price),max(sc.price)) when 1 then min(sc.price)  end  from sku_content sc where sc.goods_id=g.id) ");
		sql.append(" end)");
		sql.append(" sku_range_price");
		
		sql.append(" from goods g");
		sql.append(" left join brand b on g.brand_id=b.id ");
		sql.append(" left join type t on g.type_id=t.id ");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.type=?");
		sql.append(" where g.shop_id=?");
		
		StatementParameter params=new StatementParameter();
		params.setInt(GoodsImagesType.COVER.toIntStatus());
		params.setString(glb.getShopId());
		
		if(StringUtils.isNoneBlank(glb.getName())){
			sql.append(" and g.name like ?");
			params.setString("%"+glb.getName()+"%");
		}
		if(StringUtils.isNotBlank(glb.getTypeId())){
			sql.append(" and g.type_id=?");
			params.setString("%"+glb.getTypeId()+"%");
		}
		if(StringUtils.isNoneBlank(glb.getBrandId())){
			sql.append(" and g.brand_id=?");
			params.setString("%"+glb.getBrandId()+"%");
		}
		
		
		if (pageNumber != null && maxPageRow != null) {
			return super.queryForPagingList(sql.toString(), GoodsBO.class, params, pageNumber, maxPageRow);
		} else {
			return super.queryForList(sql.toString(), GoodsBO.class, params);
		}
		
	}

	@Override
	public int getTotalCountByShopId(GoodsListBO glb) {
		StringBuffer sql=new StringBuffer();
		sql.append("select count(*)");
		sql.append(" from goods g");
		sql.append(" left join brand b on g.brand_id=b.id ");
		sql.append(" left join type t on g.type_id=t.id ");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.type=?");
		sql.append(" where g.shop_id=?");
		
		StatementParameter params=new StatementParameter();
		params.setInt(GoodsImagesType.COVER.toIntStatus());
		params.setString(glb.getShopId());
		
		if(StringUtils.isNoneBlank(glb.getName())){
			sql.append(" and g.name like ?");
			params.setString("%"+glb.getName()+"%");
		}
		if(StringUtils.isNotBlank(glb.getTypeId())){
			sql.append(" and g.type_id=?");
			params.setString("%"+glb.getTypeId()+"%");
		}
		if(StringUtils.isNoneBlank(glb.getBrandId())){
			sql.append(" and g.brand_id=?");
			params.setString("%"+glb.getBrandId()+"%");
		}
		return queryForInt(sql.toString(),params);
	}
	
	
	@Override
	public List<ShopWithinDistanceBO> withinDistanceShop(Double log, Double lat, Double distance) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append("select id,code,name,linkman,mobile_phone,address,lat,lng,shop_head_img_url,status,type,collections,description,opening_days,opening_hours,background_image,is_shop,wechat_code_url,is_chain,chain_id,commend_money, ");
		sql.append(" sqrt( pow(111.2 *(lat - ?), 2) + pow( 111.2 * (? - lng) * cos(lat / 57.3), 2 )) as distance from shop  ");
		sql.append(" where is_delete = ? ");
		sql.append(" and is_platform = ? ");
		sql.append(" and status != ? ");
		sql.append(" having distance < ? ");
		
		params.setDouble(log);
		params.setDouble(lat);
		params.setInt(Is.NO.toIntValue());
		params.setInt(Is.YES.toIntValue());
		params.setInt(ShopStatus.STOP.toIntStatus());
		params.setDouble(distance);
		
		sql.append("order by distance");
		
		return queryForList(sql.toString(), ShopWithinDistanceBO.class, params);
	}
	
	@Override
	public List<Shop> findTop200ByTime(String time) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		
		sql.append("SELECT * ");
		sql.append("FROM shop ");
		sql.append("WHERE area_province_id IS NOT NULL ");
		sql.append("AND area_city_id IS NOT NULL ");
		sql.append("AND area_county_id IS NOT NULL ");
		sql.append("AND audit_status = ? ");
		sql.append("AND created_time >= ? ");
		sql.append("ORDER BY created_time ASC ");
		sql.append("LIMIT 200 ");
		
		params.setInt(AuditStatus.AUDITSUCCESS.toIntStatus());
		params.setString(time);
		
		return queryForList(sql.toString(), Shop.class, params);
	}
	
	@Override
	public ShopBO aroundShop(Double lat,Double log,String chainId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();

		
		sql.append(" select s.id as shopId,s.background_image,s.shop_head_img_url,s.collections,s.opening_days, ");
		sql.append(" s.name,s.address,s.description,s.mobile_phone,i.full_name as industryName,s.opening_hours,");
		sql.append("s.status,s.type,s.industry_id,s.banner_images,s.is_platform,");
		sql.append("(select name from area where is_delete=? and id=s.area_province_id) 'province',");
		sql.append("(select name from area where is_delete=? and id=s.area_city_id) 'city',s.lat,s.lng,");
		sql.append("(select name from area where is_delete=? and id=s.area_county_id) 'county' ");
		
		sql.append(" ,sqrt( pow(111.2 *(s.lat - ?), 2) + pow( 111.2 * (? - s.lng) * cos(s.lat / 57.3), 2 )) as distance from shop s ");
		sql.append(" inner join industry i on i.id = s.industry_id ");
		
		sql.append(" where s.is_delete = ? ");
//		sql.append(" and s.is_platform = ? ");
		sql.append(" and s.status != ? ");
		sql.append(" and s.chain_id = ? ");
		
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		
		params.setDouble(log);
		params.setDouble(lat);
		params.setInt(Is.NO.toIntValue());
//		params.setInt(Is.YES.toIntValue());
		params.setInt(ShopStatus.STOP.toIntStatus());
		params.setString(chainId);
		
		sql.append("order by distance asc limit 1");
		
		return super.query(sql.toString(), ShopBO.class, params);
	}

	@Override
	public boolean updateIsOpenSell() {
		StringBuffer sql = new StringBuffer();
		sql.append("update shop set is_open_sell =? where is_open_sell =?");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.NO.toIntValue());
		params.setInt(Is.YES.toIntValue());
		return super.updateForBoolean(sql.toString(), params);
	}

	@Override
	public boolean updateIsOpenRebate() {
		StringBuffer sql = new StringBuffer();
		sql.append("update shop set is_open_rebate =? where is_open_rebate =?");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.NO.toIntValue());
		params.setInt(Is.YES.toIntValue());
		return super.updateForBoolean(sql.toString(), params);
	}
	
	@Override
	public List<Shop> getProgramShop(){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from shop where is_delete = ? and is_expire = ? and is_platform=?");
		StatementParameter params=new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(Is.NO.toIntValue());
		params.setInt(ShopIsPlatform.OWN.toIntStatus());
		return super.queryForList(sql.toString(),Shop.class, params);
		
	}
	@Override
	public List<Shop> getShopList(String chainId) {
		StringBuffer sql=new StringBuffer();
		StatementParameter params=new StatementParameter();
		sql.append("select *");
		sql.append(" from shop s");
		sql.append(" where s.chain_id=?");
		params.setString(chainId);
		return super.queryForList(sql.toString(), Shop.class, params);
	}

	@Override
	public List<ShopBO> chainShop(Double lat, Double log, String chainId, String areaCityId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();

		
		sql.append(" select s.id as shopId,s.background_image,s.shop_head_img_url,s.collections,s.opening_days, ");
		sql.append(" s.name,s.address,s.description,s.mobile_phone,i.full_name as industryName,s.opening_hours,");
		sql.append("s.status,s.type,s.industry_id,s.banner_images,s.is_platform,");
		sql.append("(select name from area where is_delete=? and id=s.area_province_id) 'province',");
		sql.append("(select name from area where is_delete=? and id=s.area_city_id) 'city',s.lat,s.lng,");
		sql.append("(select name from area where is_delete=? and id=s.area_county_id) 'county' ");
		
		sql.append(" ,sqrt( pow(111.2 *(s.lat - ?), 2) + pow( 111.2 * (? - s.lng) * cos(s.lat / 57.3), 2 )) as distance from shop s ");
		sql.append(" inner join industry i on i.id = s.industry_id ");
		
		sql.append(" where s.is_delete = ? ");
//		sql.append(" and s.is_platform = ? ");
		sql.append(" and s.status != ? ");
		sql.append(" and s.chain_id = ? ");
		sql.append(" and s.area_city_id = ? ");
		
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		
		params.setDouble(log);
		params.setDouble(lat);
		params.setInt(Is.NO.toIntValue());
//		params.setInt(Is.YES.toIntValue());
		params.setInt(ShopStatus.STOP.toIntStatus());
		params.setString(chainId);
		params.setString(areaCityId);
		
		sql.append("order by distance asc ");
		
		return super.queryForList(sql.toString(), ShopBO.class, params);
	}

	@Override
	public List<Shop> getShop(Iterator<String> shopIdList) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append(" select * from shop where id in (");
		while (shopIdList.hasNext()) {
			sql.append("?,");
			params.setString(shopIdList.next());
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		return super.queryForList(sql.toString(), Shop.class, params);
	}

	@Override
	public List<Shop> getList(ShopBO sb, int pageNumber, int maxPageRow) {
		StringBuffer sql=new StringBuffer();
		StatementParameter param=new StatementParameter();
		sql.append("select");
		sql.append(" s.*");
		sql.append(" from");
		sql.append(" shop s");
		sql.append(" where s.is_delete=? and s.is_platform=? and s.id not in(select ms.cs_id from modules_shops ms where ms.is_delete=? and ms.modules_id=?) and s.audit_status=?");
		param.setInt(Is.NO.toIntValue());
		param.setInt(ShopIsPlatform.HEADQUARTERS.toIntStatus());
		param.setInt(Is.NO.toIntValue());
		param.setString(sb.getModulesId());
		param.setInt(AuditStatus.AUDITSUCCESS.toIntStatus());
		if(StringUtils.isNoneBlank(sb.getName())){
			sql.append(" and s.name like ?");
			param.setString("%"+sb.getName()+"%");
		}
		if(sb.getStatus()!=null){
			sql.append(" and s.status=?");
			param.setInt(sb.getStatus());
		}
		return super.queryForPagingList(sql.toString(), Shop.class, param, pageNumber,maxPageRow);
	}

	@Override
	public Integer getListCount(ShopBO sb) {
		StringBuffer sql=new StringBuffer();
		StatementParameter param=new StatementParameter();
		sql.append("select");
		sql.append(" count(*)");
		sql.append(" from");
		sql.append(" shop s");
		sql.append(" where s.is_delete=? and s.is_platform=? and s.id not in(select ms.cs_id from modules_shops ms where ms.is_delete=? and ms.modules_id=?) and s.audit_status=?");
		param.setInt(Is.NO.toIntValue());
		param.setInt(ShopIsPlatform.HEADQUARTERS.toIntStatus());
		param.setInt(Is.NO.toIntValue());
		param.setString(sb.getModulesId());
		param.setInt(AuditStatus.AUDITSUCCESS.toIntStatus());
		if(StringUtils.isNoneBlank(sb.getName())){
			sql.append(" and s.name like ?");
			param.setString("%"+sb.getName()+"%");
		}
		if(sb.getStatus()!=null){
			sql.append(" and s.status=?");
			param.setInt(sb.getStatus());
		}
		return super.queryForInt(sql.toString(), param);
	}
	
}
